using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Globalization;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using Colorprj.Common;
namespace Colorprj.Masters
{
    class SubProductManager
    {

        public bool AddSubProduct(int productId, string SubProductName, decimal OpBal, decimal PurRate, decimal SaleRate, string Units)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "AddSubProduct";
            DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(addGroupCommand, "productId", DbType.Int16, productId);
            db.AddInParameter(addGroupCommand, "SubProductName", DbType.String, SubProductName);
            //db.AddInParameter(addGroupCommand, "ShortName", DbType.String, spEntity.ShortName);
            db.AddInParameter(addGroupCommand, "OpBal", DbType.Double, OpBal);
            db.AddInParameter(addGroupCommand, "PurRate", DbType.Double, PurRate);
            db.AddInParameter(addGroupCommand, "SaleRate", DbType.Double, SaleRate);
            db.AddInParameter(addGroupCommand, "Units", DbType.String, Units);


          


            //try
            //{
            // Credit the first account
            db.ExecuteNonQuery(addGroupCommand);


            result = true;
            //}
            //catch
            //{

            //}
            return result;
        }
        public bool AddSubProduct(SubProduct spEntity)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                   

                    // Two operations, one to credit an account, and one to debit another
                    // account.
                    string sqlCommand = "AddSubProduct";
                    DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
                    db.AddInParameter(addGroupCommand, "productId", DbType.Int16, spEntity.ProductId);
                    db.AddInParameter(addGroupCommand, "SubProductName", DbType.String, spEntity.SubProductName);
                    db.AddInParameter(addGroupCommand, "ShortName", DbType.String, spEntity.ShortName);
                    db.AddInParameter(addGroupCommand, "OpBal", DbType.Double, spEntity.OpBal);
                    db.AddInParameter(addGroupCommand, "PurRate", DbType.Double, spEntity.PurRate);
                    db.AddInParameter(addGroupCommand, "SaleRate", DbType.Double, spEntity.SaleRate);
                    db.AddInParameter(addGroupCommand, "Units", DbType.String, spEntity.Units);
                    db.AddInParameter(addGroupCommand, "ReOrderLvl", DbType.Double, spEntity.ReOrderLvl);
                    //int SubProductId = Convert.ToInt16(db.ExecuteScalar(addGroupCommand, transaction));

                    //string sqlUpdStockCommand = "UpdStock";
                    //DbCommand stpckUpdCommand = db.GetStoredProcCommand(sqlUpdStockCommand);
                    //db.AddInParameter(stpckUpdCommand, "SubProductid", DbType.Int16, SubProductId);
                    //db.AddInParameter(stpckUpdCommand, "OpBal", DbType.Int16, spEntity.OpBal);
                    //db.AddInParameter(stpckUpdCommand, "PurQty", DbType.Int16, 0);
                    //db.AddInParameter(stpckUpdCommand, "ChallanQty", DbType.Int16, 0);
                    //db.AddInParameter(stpckUpdCommand, "InvoiceQty", DbType.Int16, 0);
                    //db.AddInParameter(stpckUpdCommand, "PurReturn", DbType.Int16, 0);
                    //db.AddInParameter(stpckUpdCommand, "SaleReturn", DbType.Int16, 0);


                  

                    // Credit the first account
                    db.ExecuteNonQuery(addGroupCommand, transaction);

                    transaction.Commit();  
                    result = true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();  
                    throw new Exception(ex.Message);
                }
            }
            return result;
        }



        public bool UpdSubProduct(SubProduct spEntity)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdSubProduct";
            DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
            //db.AddInParameter(addGroupCommand, "productId", DbType.Int16, spEntity.ProductId );
            db.AddInParameter(addGroupCommand, "SubProductName", DbType.String, spEntity.SubProductName );
            db.AddInParameter(addGroupCommand, "ShortName", DbType.String, spEntity.ShortName);
            db.AddInParameter(addGroupCommand, "OpBal", DbType.Double, spEntity.OpBal );
            db.AddInParameter(addGroupCommand, "ReOrderLvl", DbType.Double, spEntity.ReOrderLvl);
            db.AddInParameter(addGroupCommand, "PurRate", DbType.Double, spEntity.PurRate );
            db.AddInParameter(addGroupCommand, "SaleRate", DbType.Double, spEntity.SaleRate );
            db.AddInParameter(addGroupCommand, "Units", DbType.String, spEntity.Units );
            db.AddInParameter(addGroupCommand, "Id", DbType.String, spEntity.SubProductId);

            //try
            //{
            // Credit the first account
            db.ExecuteNonQuery(addGroupCommand);


            result = true;
            //}
            //catch
            //{

            //}
            return result;
        }

        public bool UpdProduct(Product spEntity)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdProduct";
            DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(addGroupCommand, "ProductName", DbType.String, spEntity.ProductName);
            db.AddInParameter(addGroupCommand, "Id", DbType.String, spEntity.ProductId);

            //try
            //{
            // Credit the first account
            db.ExecuteNonQuery(addGroupCommand);


            result = true;
            //}
            //catch
            //{

            //}
            return result;
        }
        public void DelSubProduct(int Id)
        {

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            try {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "DelSubProduct";
            DbCommand selSPCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(selSPCommand, "Id", DbType.Int32, Id);

            DataSet PurchaseDataSet = db.ExecuteDataSet(selSPCommand);
           }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);  
            }
          
        }


        public SubProduct  GetSubProductById(int Id)
        {

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelSubProduct";
            DbCommand selSPCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(selSPCommand, "Id", DbType.Int32, Id);

            DataSet PurchaseDataSet = db.ExecuteDataSet(selSPCommand);
            SubProduct subProductEntity = new SubProduct();
            
            foreach (DataRow dr in PurchaseDataSet.Tables[0].Rows)
            {
                //subProductEntity.ProductId  = Convert.ToInt16(dr["PartyId"]);
                //subProductEntity = new SubProduct() ;
                subProductEntity.ProductId = Convert.ToInt16(dr["ProductId"]);
                subProductEntity.SubProductId  = Convert.ToInt16(dr["SubproductId"]);
                subProductEntity.Units = dr["Units"].ToString();
                subProductEntity.OpBal = Convert.ToDouble(dr["OpBal"]);
                subProductEntity.PurRate = Convert.ToDouble(dr["PurRate"]);
                subProductEntity.SaleRate = Convert.ToDouble(dr["SaleRate"]);
                subProductEntity.SubProductName = dr["SubproductName"].ToString() ;
                subProductEntity.ShortName = dr["ShortName"].ToString();
                subProductEntity.Stock = Convert.ToDouble (dr["BalQty"]);
                subProductEntity.ReOrderLvl  = Utility.getDblVal(dr["ReOrderLevel"]);
            }

            return subProductEntity;
        }



        public DataSet GetAllProducts()
        {
            // Create the Database object, using the default database service. The
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelAllProduct";
            DbCommand selSPCommand = db.GetStoredProcCommand(sqlCommand);
            // DataSet that will hold the returned results		
            DataSet productsDataSet = null;

            productsDataSet = db.ExecuteDataSet(selSPCommand);

            // Note: connection was closed by ExecuteDataSet method call 

            return productsDataSet;
        }

        public DataSet GetAllOnlyProducts()
        {
            // Create the Database object, using the default database service. The
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelAllOnlyProduct";
            DbCommand selSPCommand = db.GetStoredProcCommand(sqlCommand);
            // DataSet that will hold the returned results		
            DataSet productsDataSet = null;

            productsDataSet = db.ExecuteDataSet(selSPCommand);

            // Note: connection was closed by ExecuteDataSet method call 

            return productsDataSet;
        }



        public bool AddProduct(string ProductName)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "AddProduct";
            DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(addGroupCommand, "ProductName", DbType.String, ProductName);
          


            try
            {
                // Credit the first account
                db.ExecuteNonQuery(addGroupCommand);


                result = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }



          
            return result;
        }

    }
}
